package com.sunni.repository.impl;

import com.sunni.entity.Book;
import com.sunni.entity.Borrow;
import com.sunni.entity.Reader;
import com.sunni.repository.BorrowRepository;
import com.sunni.utils.JDBCTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BorrowRepositoryImpl implements BorrowRepository {

    private Connection connection= null;
    private PreparedStatement preparedStatement =null;
    private  ResultSet resultSet=null;

    /**
     *  借阅图书，把借阅信息添加进borrow表
     * @param bookId
     * @param readId
     * @param borrowTime
     * @param returnTime
     * @param adminId
     * @param state
     * @return
     */
    @Override
    public Integer insertBorrow(Integer bookId, Integer readId, String borrowTime, String returnTime, Integer adminId, Integer state) {
        Integer resulit = null;
        try {
            connection = JDBCTool.getConnection();
            String sql = "INSERT INTO `library`.`borrow` (`bookid`, `readerid`, `borrowtime`, `returntime`, `adminid`, `state`) VALUES (?, ?, ?, ?,NULL,0)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,bookId);
            preparedStatement.setInt(2,readId);
            preparedStatement.setString(3,borrowTime);
            preparedStatement.setString(4,returnTime);
            resulit = preparedStatement.executeUpdate();
        }catch (SQLException e){
            System.out.println(e);
        }finally {
            JDBCTool.close(connection,preparedStatement,resultSet);
        }

        return resulit;
    }

    @Override
    public List<Borrow> findAllByReaderid(Integer readerId,Integer index,Integer LIMIT) {
        List<Borrow> borrowList = new ArrayList<>();

        try {
            connection = JDBCTool.getConnection();
            String sql = "select br.id,b.name,b.author,b.publish,r.name,r.tel,r.cardid,br.borrowtime,br.returntime,br.state,br.adminid from borrow br,reader r,book b where readerid=? and br.bookid=b.id and br.readerid=r.id limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,readerId);
            preparedStatement.setInt(2,index);
            preparedStatement.setInt(3,LIMIT);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                new Book();
                new Reader();
                borrowList.add(new Borrow(
                        resultSet.getInt(1),
                        new Book(resultSet.getString(2),resultSet.getString(3),resultSet.getString(4)),
                        new Reader(resultSet.getString(5),resultSet.getString(6),resultSet.getString(7)),
                        resultSet.getString(8),
                        resultSet.getString(9),
                        resultSet.getInt(10),
                        resultSet.getInt(11)
                                        )
                            );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTool.close(connection,preparedStatement,resultSet);
        }

        return borrowList;
    }

    /**
     *  获取 某读者的总借阅量 （总借阅量 / 每页展示数量 = 页数） ，根据 读者id
     * @param readerId
     * @return
     */
    @Override
    public Integer countBorrowByReaderid(Integer readerId) {
        Integer count = null;

        try {
            connection= JDBCTool.getConnection();
            String sql = "select count(*) 条数 from borrow where readerid=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,readerId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTool.close(connection,preparedStatement,resultSet);
        }

        return count;
    }

    @Override
    public Integer countBorrowByState0(Integer state) {
        Integer count = null;
        try {
            connection = JDBCTool.getConnection();
            String sql = "select count(*) 条数 from borrow where state=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,state);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            System.out.println(e);
        } finally {
            JDBCTool.close(connection,preparedStatement,resultSet);
        }
        return count;
    }

    @Override
    public List<Borrow> findAllByState0(Integer state0, Integer index,Integer LIMIT) {
        List<Borrow> borrowListByState0 = new ArrayList<>();

        try {
            connection = JDBCTool.getConnection();
            String sql = "select br.id,b.name,b.author,b.publish,r.name,r.tel,r.cardid,br.borrowtime,br.returntime,br.state,br.adminid from borrow br,reader r,book b where state=? and br.bookid=b.id and br.readerid=r.id limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,state0);
            preparedStatement.setInt(2,index);
            preparedStatement.setInt(3,LIMIT);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                new Book();
                new Reader();
                borrowListByState0.add(new Borrow(
                                resultSet.getInt(1),
                                new Book(resultSet.getString(2),resultSet.getString(3),resultSet.getString(4)),
                                new Reader(resultSet.getString(5),resultSet.getString(6),resultSet.getString(7)),
                                resultSet.getString(8),
                                resultSet.getString(9),
                                resultSet.getInt(10),
                                resultSet.getInt(11)
                        )
                );
            }
        } catch (SQLException e) {
            System.out.println(e);
        } finally {
            JDBCTool.close(connection,preparedStatement,resultSet);
        }
        return borrowListByState0;
    }

    public Integer handleByBorrowid(Integer borrowId,Integer state,Integer adminId){
        Integer result = null;

        try {
            connection = JDBCTool.getConnection();
            String sql = "update `borrow` set `adminid` = ?, `state` = ? where `id` = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,adminId);
            preparedStatement.setInt(2,state);
            preparedStatement.setInt(3,borrowId);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
        }finally {
            JDBCTool.close(connection,preparedStatement,resultSet);
        }
        return result;
    }

}
